﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NoORM.Core.DbAcceess;
using NoORM.Core.DbDialect;
using NoORM.Core.Contract;

namespace NoORM.Core.Service
{
    /// <summary>
    /// 增删改查服务
    /// </summary>
    public class CrudService
    {
        /// <summary>
        /// 数据库操作对象
        /// </summary>
        private readonly Dao dao;

        /// <summary>
        /// 数据库方言构建者
        /// </summary>
        private readonly DbDialectBuilder dialectBuilder;

        public CrudService(Dao dao, DbDialectBuilder dialectBuilder)
        {
            this.dao = dao;
            this.dialectBuilder = dialectBuilder;
        }

        /// <summary>
        /// 拼凑where条件
        /// </summary>
        /// <param name="dialect">数据库方言</param>
        /// <param name="queries">查询条件</param>
        /// <param name="parameters">查询参数</param>
        /// <returns></returns>
        private string GetWhereString(IDbDialect dialect, List<QueryExpression> queries,ref Dictionary<string, object> parameters)
        {
            StringBuilder sql = new StringBuilder();
            foreach (QueryExpression expression in queries)
            {
                string fieldName = dialect.WrapFieldName(expression.Field); //用方言包裹字段名
                sql.Append($" {expression.Prefix} ");       //and or
                sql.Append(expression.LeftBrackets);        //左括号
                sql.Append(fieldName);                      //字段名称
                sql.Append($" {expression.Operator} ");     //操作符
                if (expression.Operator.Equals("like", StringComparison.CurrentCultureIgnoreCase))
                {
                    sql.Append($"CONCAT(CONCAT('%',#{expression.Field}__#),'%')"); // FieldName LIKE '%abc%'
                    parameters.Add($"{expression.Field}__", expression.Value);
                }
                else if (expression.Operator.Equals("in", StringComparison.CurrentCultureIgnoreCase) || expression.Operator.Equals("not in", StringComparison.CurrentCultureIgnoreCase))
                {
                    sql.Append("(");
                    int i = 0;
                    foreach (dynamic item in expression.Value)
                    {
                        sql.Append($"#{expression.Field}__{i}#,");
                        parameters.Add($"{expression.Field}__{i}", item.Value);
                        i++;
                    }
                    sql.Remove(sql.Length - 1, 1);
                    sql.Append(")");
                }
                else
                {
                    sql.Append($"#{expression.Field}__#");//参数化字段名称
                    parameters.Add($"{expression.Field}__", expression.Value);
                }
                sql.Append(expression.RightBrackets);       //右括号
            }
            return sql.ToString();
        }

        /// <summary>
        /// 创建数据
        /// </summary>
        /// <param name="config">连接字符串</param>
        /// <param name="table">表名</param>
        /// <param name="keyValues">新增的数据行，注意不能包含自增列</param>
        /// <returns></returns>
        public async Task<int> CreateAsync(ConnectionStringConfig config, string table, Dictionary<string, object> keyValues)
        {
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            table = dialect.WrapFieldName(table);
            StringBuilder sql = new StringBuilder();
            sql.Append($"INSERT INTO {table} (");
            foreach (var item in keyValues)
            {
                string fieldName = dialect.WrapFieldName(item.Key);
                sql.Append($"{fieldName},");
            }
            sql.Remove(sql.Length - 1, 1);
            sql.Append(") VALUES (");
            foreach (var item in keyValues)
            {
                sql.Append($"#{item.Key}#,");
            }
            sql.Remove(sql.Length - 1, 1);
            sql.Append(");");

            int result = await this.dao.ExecuteNoQueryAsync(config, sql.ToString(), keyValues);
            return result;
        }

        /// <summary>
        /// 读取一行数据
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="table">表名</param>
        /// <param name="request">sql请求</param>
        /// <returns></returns>
        public async Task<Dictionary<string, object>> ReadAsync(ConnectionStringConfig config, string table, SqlRequest request)
        {
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            table = dialect.WrapFieldName(table);
            StringBuilder selectString = new StringBuilder();
            if(request.Fields==null || !request.Fields.Any())
            {
                selectString.Append("*");
            }
            else
            {
                foreach (string item in request.Fields)
                {
                    selectString.Append($"{dialect.WrapFieldName(item)},");
                }
                selectString.Remove(selectString.Length - 1, 1);
            }
            StringBuilder sql = new StringBuilder($"SELECT {selectString} FROM {table} WHERE");
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            string whereString = this.GetWhereString(dialect, request.Queries,ref parameters);
            sql.Append(whereString);
            sql.Append(";");
            Dictionary<string, object> result = await this.dao.ExecuteDictionaryAsync(config, sql.ToString(), parameters);
            return result;
        }

        /// <summary>
        /// 根据查询表达式查询数
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="table">表名</param>
        /// <param name="request">sql请求</param>
        /// <returns></returns>
        public async Task<List<Dictionary<string, object>>> SelectAsync(ConnectionStringConfig config, string table, SqlRequest request)
        {
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            table = dialect.WrapFieldName(table);
            StringBuilder selectString = new StringBuilder();
            if (request.Fields == null || !request.Fields.Any())
            {
                selectString.Append("*");
            }
            else
            {
                foreach (string item in request.Fields)
                {
                    selectString.Append($"{dialect.WrapFieldName(item)},");
                }
                selectString.Remove(selectString.Length - 1, 1);
            }
            StringBuilder sql = new StringBuilder($"SELECT {selectString} FROM {table}");
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            if (request.Queries != null)
            {
                sql.Append(" WHERE");
                string whereString = this.GetWhereString(dialect, request.Queries,ref parameters);
                sql.Append(whereString);
            }
            sql.Append(";");
            List<Dictionary<string, object>> result = await this.dao.ExecuteListAsync(config, sql.ToString(), parameters);
            return result;
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="table">表名</param>
        /// <param name="request">sql请求</param>
        /// <param name="entity">修改后的数据</param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(ConnectionStringConfig config, string table, SqlRequest request)
        {
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            table = dialect.WrapFieldName(table);
            StringBuilder sql = new StringBuilder($"UPDATE {table} SET ");
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            foreach (var item in request.Data)
            {
                string fieldName = dialect.WrapFieldName(item.Key);
                sql.Append($"{fieldName}=#{item.Key}#,");
                parameters.Add(item.Key, item.Value);
            }
            sql.Remove(sql.Length - 1, 1);
            sql.Append(" WHERE");
            string whereString = this.GetWhereString(dialect, request.Queries,ref parameters);
            sql.Append(whereString);
            sql.Append(";");
            int result = await this.dao.ExecuteNoQueryAsync(config, sql.ToString(), parameters);
            return result;
        }

       

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="table">表名</param>
        /// <param name="parameters">查询参数</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(ConnectionStringConfig config, string table, List<QueryExpression> queries)
        {
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            table = dialect.WrapFieldName(table);
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            StringBuilder sql = new StringBuilder($"DELETE {dialect.DeleteFrom()} {table} WHERE");
            string expressionString = this.GetWhereString(dialect, queries,ref parameters);
            sql.Append(expressionString);
            sql.Append(";");
            int result = await this.dao.ExecuteNoQueryAsync(config, sql.ToString(), parameters);
            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="table">表名</param>
        /// <param name="request">sql请求</param>
        /// <returns></returns>
        public async Task<PageResponse> PageAsync(ConnectionStringConfig config, string table, SqlRequest request)
        {
            PageResponse response = new PageResponse();
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            table = dialect.WrapFieldName(table);
            StringBuilder selectString = new StringBuilder();
            if (request.Fields == null || !request.Fields.Any())
            {
                selectString.Append("*");
            }
            else
            {
                foreach (string item in request.Fields)
                {
                    selectString.Append($"{dialect.WrapFieldName(item)},");
                }
                selectString.Remove(selectString.Length - 1, 1);
            }
            StringBuilder sql = new StringBuilder($"SELECT {selectString} FROM {table}");
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            if (request.Queries != null && request.Queries.Any())
            {
                sql.Append(" WHERE ");
                string expressionString = this.GetWhereString(dialect, request.Queries,ref parameters);
                sql.Append(expressionString);
            }
            string rowCountSql = $"SELECT COUNT(1) FROM ({sql}) TEMP;";
            object o = await dao.ExecuteScalarAsync(config, rowCountSql, parameters);
            if (Convert.IsDBNull(o))
            {
                response.Success = false;
                response.Message = "没有数据";
                return response;
            }
            response.RowCount = Convert.ToInt32(o);
            string cammandText = dialect.GetPageSql(sql.ToString(), request.OrderBy, request.PageSize, request.PageIndex) + ";";
            response.Data = await dao.ExecuteListAsync(config, cammandText, parameters);
            response.Success = true;
            return response;
        }

       

        /// <summary>
        /// 统计
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="table">表名</param>
        /// <param name="request">sql请求</param>
        /// <returns></returns>
        public async Task<List<Dictionary<string, object>>> Func(ConnectionStringConfig config, string table, SqlRequest request)
        {
            List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            table = dialect.WrapFieldName(table);
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            StringBuilder sql = new StringBuilder($"SELECT ");
            if (request.GroupFields != null && request.GroupFields.Any())
            {
                foreach (string field in request.GroupFields)
                {
                    string fieldName = dialect.WrapFieldName(field);
                    sql.Append($"{fieldName},");
                }
            }
            foreach (FuncExpression func in request.Functions)
            {
                string fieldName = dialect.WrapFieldName(func.Field);
                string resultName = string.IsNullOrEmpty(func.ResultName) ? fieldName : dialect.WrapFieldName(func.ResultName);
                sql.Append($"{func.FunctionName}({fieldName}) {resultName},");
            }
            sql.Remove(sql.Length - 1, 1);
            sql.Append($" FROM {table}");
            if (request.Queries != null && request.Queries.Any())
            {
                sql.Append(" WHERE ");
                string expressionString = this.GetWhereString(dialect, request.Queries,ref parameters);
                sql.Append(expressionString);
            }
            if (request.GroupFields != null && request.GroupFields.Any())
            {
                sql.Append(" GROUP BY ");
                foreach (string field in request.GroupFields)
                {
                    string fieldName = dialect.WrapFieldName(field);
                    sql.Append($"{fieldName},");
                }
            }
            sql.Remove(sql.Length - 1, 1);
            sql.Append(";");
            result = await dao.ExecuteListAsync(config, sql.ToString(), parameters);
            return result;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="tranRequest">事务请求数据</param>
        /// <returns></returns>
        public async Task<bool> Trans(ConnectionStringConfig config, List<TransactionRequest> tranRequest)
        {
            List<Tuple<string, Dictionary<string, object>>> transItems = new List<Tuple<string, Dictionary<string, object>>>();
            IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
            foreach (TransactionRequest item in tranRequest)
            {
                string tableName = dialect.WrapFieldName(item.Table);
                if (item.Operator.Equals("insert", StringComparison.CurrentCultureIgnoreCase))
                {
                    StringBuilder sql = new StringBuilder();
                    sql.Append($"INSERT INTO {tableName} (");
                    foreach (var keyvalues in item.Data)
                    {
                        string fieldName = dialect.WrapFieldName(keyvalues.Key);
                        sql.Append($"{fieldName},");
                    }
                    sql.Remove(sql.Length - 1, 1);
                    sql.Append(") VALUES (");
                    foreach (var keyvalues in item.Data)
                    {
                        sql.Append($"#{keyvalues.Key}#,");
                    }
                    sql.Remove(sql.Length - 1, 1);
                    sql.Append(");");
                    Tuple<string, Dictionary<string, object>> tuple = new Tuple<string, Dictionary<string, object>>(sql.ToString(), item.Data);
                    transItems.Add(tuple);
                }
                else if (item.Operator.Equals("delete", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (item.Queries == null || !item.Queries.Any())
                    {
                        throw new Exception("服务器拒绝了无条件的delete指令");
                    }
                    Dictionary<string, object> parameters = new Dictionary<string, object>();
                    StringBuilder sql = new StringBuilder($"DELETE {dialect.DeleteFrom()} {tableName} WHERE");
                    string whereString = this.GetWhereString(dialect, item.Queries, ref parameters);
                    sql.Append(whereString);
                    sql.Append(";");
                    Tuple<string, Dictionary<string, object>> tuple = new Tuple<string, Dictionary<string, object>>(sql.ToString(), parameters);
                    transItems.Add(tuple);
                }
                else if (item.Operator.Equals("update", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (item.Queries == null || !item.Queries.Any())
                    {
                        throw new Exception("服务器拒绝了无条件的update指令");
                    }
                    StringBuilder sql = new StringBuilder($"UPDATE {tableName} SET ");
                    Dictionary<string, object> parameters = new Dictionary<string, object>();
                    foreach (var keyValues in item.Data)
                    {
                        string fieldName = dialect.WrapFieldName(keyValues.Key);
                        sql.Append($"{fieldName}=#{keyValues.Key}#,");
                        parameters.Add(keyValues.Key, keyValues.Value);
                    }
                    sql.Remove(sql.Length - 1, 1);
                    sql.Append(" WHERE");
                    string whereString = this.GetWhereString(dialect, item.Queries, ref parameters);
                    sql.Append(";");
                    Tuple<string, Dictionary<string, object>> tuple = new Tuple<string, Dictionary<string, object>>(sql.ToString(), parameters);
                    transItems.Add(tuple);
                }
            }
            bool result = await this.dao.ExecuteTransaction(config, transItems);
            return result;
        }
    }
}
